package edu.byu.isys413.mystuff;
/*
 * membershipInterestDAO.java
 * Author : abasnet
 *
 * Created on February 15, 2007, 8:31 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

import java.util.*;
import java.sql.*;

public class membershipInterestDAO{
   
    private static membershipInterestDAO instance;
    private static Cache cache;
    
    /** Creates a new instance of customerDAO */
    public membershipInterestDAO() {
        cache = Cache.getInstance();
     
    }
    
    public static synchronized membershipInterestDAO getInstance() {
        if (instance == null) {
            instance = new membershipInterestDAO();            
        }
        return instance;
    }
    
    
    public ArrayList<interestBO> readInterests() throws DataException{
    ArrayList<interestBO> interests = new ArrayList();
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM INTEREST_TBL");
            
            while(rs.next()){
               
                interestBO intr = new interestBO(rs.getString("INTEREST_ID"));
                intr.setDescription(rs.getString("DESCRIPTION"));
                interests.add(intr);
               
            }
            
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        return interests;
    }
    
    
     public ArrayList<interestBO> readInterests(String membID) throws DataException{
    ArrayList<interestBO> interests = new ArrayList();
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM INTEREST_TBL WHERE MEMBERSHIP_ID ='"+membID+"'");
            
            while(rs.next()){
               
                interestBO intr = new interestBO(rs.getString("INTEREST_ID"));
                intr.setDescription(rs.getString("DESCRIPTION"));
                interests.add(intr);
               
            }
            
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        return interests;
    }
    
        
    /////////////save////////////////////
    public synchronized boolean save(ArrayList<interestBO> interests, String memb_id) throws DataException {
        
        Connection conn = null;
        try {
            
            conn = ConnectionPool.getInstance().get();
        
            Statement st = conn.createStatement();
            
            for(int i=0 ; i < interests.size(); i++ ){
                String queryString = "INSERT INTO INTEREST_MEMBERSHIP_TBL VALUES (?,?)";
                PreparedStatement pstmt = conn.prepareStatement(queryString);
                pstmt.setString(1,interests.get(i).getID());
                pstmt.setString(2,memb_id);
                pstmt.execute();
                pstmt.close();
            }
            
            
            ///////////////////////////////////////////
            // call read with a connection (the other read method in this class)
       
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return true;
    }
    
    
    
    /////////////delete////////////////////////
    public synchronized boolean delete(ArrayList<interestBO> interests, String memb_id) throws DataException {
        
        Connection conn = null;
        try {
            
            conn = ConnectionPool.getInstance().get();
        
            Statement st = conn.createStatement();
            
            for(int i=0 ; i < interests.size(); i++ ){
                String queryString = "DELETE * FROM INTEREST_MEMBERSHIP_TBL WHERE INTERESTS_ID = ? AND MEMBERSHIP_ID = ? ";
                PreparedStatement pstmt = conn.prepareStatement(queryString);
                pstmt.setString(1,interests.get(i).getID());
                pstmt.setString(2,memb_id);
                pstmt.execute();
                pstmt.close();
            }
            
            
            ///////////////////////////////////////////
            // call read with a connection (the other read method in this class)
       
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return true;
    }
    
}
